Unlike BASE SAS tables, OLAP cubes must exist in within the metadata in order to access from any of the OLAP Viewers. In addition to having some metadata OLAP cubes have a physical file structure presence (at least for MOLAP/HOLAP because it's a different story for ROLAP). When you refresh these cubes, the physical file structure is scrapped and recreated. If something goes horrible wrong, the metadata remains but the files are simply ... gone.
From SAS OLAP Cube Studio, the icon next to the cube name changes slightly. This represents the fact that the cube exist in name alone.
But what can you do to check whether it was successful during batch processing? Fortunately, SAS users are sharers. A quick Google search on my custom Google search engine resulted in this SAS Community post on checking to see if a directory exists. You can use this outright, or if you don't have the path handy you can search through the metadata using data step functions and run the whole thing with only the cube name as input.
/*Specify the cube name*/ %let cubename = SalesSummary; /*Use the Data Step Interface to Metadata*/ data _null_; length path cubeuri diruri $256; path=""; cubeuri=""; diruri=""; /* Find the CubeURI*/ rc2=metadata_getnasn("omsobj:Cube?@Name='&cubename'","AssociatedFile",1,cubeuri); /* Find the Directory URI*/ rc3=metadata_getnasn(cubeuri,"Directories",1,diruri); /* Retrieve the DirectoryName Value*/ rc4=metadata_getattr(diruri,"DirectoryName",path); call symput('path', strip(path)); run; /*Set the SASCommunity.org Macro*/ %macro DirExist(dir) ; %LOCAL rc fileref return; %let rc = %sysfunc(filename(fileref,&dir)) ; %if %sysfunc(fexist(&fileref)) %then %let return=1; %else %let return=0; &return %mend DirExist; /*Determine if the physical path exists*/ %put %DirExist(&path\&cubename\gen0000); |
If it returns a 0, you know that the refresh code failed and there is no data there. You could use this to send an alert or to move to a different phase of the process. What would you do with this code? What other uses can you think of?
3 Comments
This is very useful. Thanks for sharing.
I was wondering whether you could use SPDE engine instead of a directory test, since a MOLAP cube is stored using SPD table format. Answer is yes : in a couple of lines, you can
1) assign the path to the cube storage path using a SPDE libref assignment ('olapcub')
2) tests the existence of the special table named 'nw0000r0' (a generic alias, it seems) in the library using EXIST + IFC
3) unassign the libref
/* 1. */
%let rc=%sysfunc(libname(olapcube,&path./&cubename,SPDE));
/* 2. */
%sysfunc(IFC( %sysfunc(exist(olapcube.nw0000r0)) EQ 1, %NRSTR( %put &cubename exists. ;), %NRSTR( %put &cubename not found. ;) ));
/* 3. */
%let rc=%sysfunc(libname(olapcube));
Using a (proc SQL) describe table statement on the table 'olapcube.nw0000r0', you can also look under the hood to see how measures, dimensions & levels are defined internally inside the cube.
Awesome tip Ronan! Thanks for sharing!
Cool trick! In SAS Enterprise Guide you can open a cube directly, without navigating metadata. This is called a "private" OLAP data source (private, I suppose, because it's not known to metadata). You need to know the OLAP server host name and port, and you need credentials to connect.
It can be used for SAS OLAP data, but more often this is used for Microsoft Analysis Services (SQL Server). Yes, you can use SAS Enterprise Guide to connect to this non-SAS OLAP source, which makes it easy to integrate other IT-managed data with your SAS process.